/**
 *  Verify if table exists.
 *
 *  @param String
 *
 *  @return Boolean
 */
function tableExists(tablename) {
    var table_exists = false;
    var existing_tables = sql("SHOW TABLES");

    for (var i = 0; i < existing_tables.length; i++) {
        if (existing_tables[i] == tablename) {
            table_exists = true;
        }
    }
    return table_exists;
}

/**
 *  Create YAMO tables on Amarok database.
 *  <p></p>
 *  
 */
function setYAMODatabase() {
    var dbName1 = "yamo_tags";
    var dbName2 = "yamo_tracktag"
    //var included = 0;

    if (!tableExists(dbName1)) {
        sql('CREATE TABLE ' + dbName1 + ' (id int(10) not null auto_increment,tag VARCHAR(80) not null,color VARCHAR(7) not null, primary key(id))');
        //included += 1;
    }
    if (!tableExists(dbName2)) {
        sql( 'CREATE TABLE ' + dbName2 + ' (track int(10) not null,tag int(10) not null, value int(10) not null, primary key(track,tag))') ;
        //included += 1;
    }
    /*if (included == 2) {
        Amarok.alert("Tabelas 'yamo_tags' e 'yamo_tracktag' criadas com sucesso")
    } else if (included == 0) {
        Amarok.alert("Banco do amarok já foi criado!");
    }*/
}

/**
 *  Delete YAMO tables on Amarok database.
 */
function deleteYamoDb() {
    sql("DROP TABLE yamo_tags");
    sql("DROP TABLE yamo_tracktag");
    Amarok.alert("Tabelas exluidas com sucesso");
}

/**
 *  
 */
function getTags() {
    query = "SELECT id,tag, color FROM yamo_tags";
    var res = sql(query);
    var resArray = new Array();
    for (i = 0 ; i < res.length; i++ ) {
        if (i % 3 == 0) {
            var resObject = new Object();
            resObject.id = res[i];
            resObject.tagName = res[i+1];
            resObject.color = res[i+2];
            resArray.push(resObject);
        }
    }
    return resArray;
}

function getValueFromTrackAndThisTag(trackID,tag) {
    var tagID = getTagID(tag);
    var query = "SELECT value from yamo_tracktag where tag = "+tagID+" and track="+trackID;
    var res = sql(query);
    if (res.length == 0) return 0;
    return res;
}

function getTrackID(trackTitle,trackAlbum) {
    var query = "SELECT a.id from tracks a JOIN albums b ON(a.album = b.id) WHERE a.title LIKE '%"+trackTitle+"%'";
    query += "  AND b.name LIKE '%"+trackAlbum+"%'";
    var result = sql(query);
    if (result.length > 0) return result;
    else return -1;
}

function getTagID(tag) {
    var query = "SELECT id from yamo_tags where tag like '%"+tag+"%'";
    return sql(query);
}

function getNameFromTagID(tagID) {
    var res = sql("SELECT tag from yamo_tags where id = "+tagID)
    return res.toString();
}

function getColorFromTagID(tagID) {
    var query = "SELECT color from yamo_tags where id = "+tagID;
    var res = sql(query);
    return res;
}

function getTagsFromTrack(trackID) {
    var query = "SELECT b.tag, a.value, b.color FROM yamo_tracktag a JOIN yamo_tags b ON (a.tag = b.id) JOIN tracks c ON (a.track = c.id)"
    query += "  WHERE c.id="+trackID;

    var res = sql(query);
    var resArray = new Array();
    for (i = 0 ; i < res.length; i++ ) {
        if (i % 3 == 0) {
            var resObject = new Object();
            resObject.tagName = res[i];
            resObject.value = res[i+1];
            resObject.color = res[i+2];
            resArray.push(resObject);
        }
    }
    return resArray;
}

function getSumMusic(trackID) {
    var query = "SELECT sum(value) from yamo_tracktag where track = "+trackID;
    var result = sql(query);
    if (result > 0) {
        return result;
    } else return 0;
}


function tagMusic(trackID, tag, value) {
    var tagID = getTagID(tag);
    var countTag = sql("SELECT count(*) from yamo_tracktag where track = "+trackID + " and tag = "+tagID);
    if (countTag > 0) {
        Amarok.alert("You've already tagged this music with this tag!");
    } else {
        var query = "INSERT into yamo_tracktag(track,tag,value) VALUES(\"" + trackID + "\", \"" + tagID + "\", \"" + value + "\")";

        var resultQuery = sql(query);
    }
}


function unTagMusic(trackID, tagID) {
    sql("DELETE FROM yamo_tracktag where track = "+trackID+" AND tag = "+tagID);
}


function insertTag(tag,color) {
    var query = "INSERT INTO yamo_tags (tag,color) VALUES (\"" + tag+"\", \""+color+"\")";
    var res = sql(query);

}


function modifyTag(tagID, name, color) {
    if (color) {
        sql("UPDATE yamo_tags SET tag = '"+name+"', color = '"+color+"' WHERE id = "+tagID);
    } else {
        sql("UPDATE yamo_tags SET tag = '"+name+"' WHERE id = "+tagID);
    }
}


function deleteTag(tag) {
    var tagid = getTagID(tag);
    var query = "DELETE from yamo_tags where id = "+tagid;
    sql(query);
    var query2 = "DELETE from yamo_tracktag where tag="+tagid;
    sql(query2);
}


function modifyValue(trackID, tagID, newValue) {
    sql("UPDATE yamo_tracktag set value = "+parseInt(newValue)+"  WHERE track = "+trackID+" AND tag = "+tagID);
}


function getAllArtists() {
    var query = "SELECT artist.name, artist.id FROM artists artist";
    var result = sql(query);
    return result;
}

function getAllAlbumsFromArtist(artistID) {
    var query = "SELECT album.name, album.id FROM albums album WHERE album.artist="+artistID;
    var result = sql(query);
    return result;
}

function getAllTracksFromAlbum(albumID) {
    var query = "SELECT track.title, track.id FROM tracks track WHERE track.album="+albumID;
    var result = sql(query);
    return result;
}

function getAllTracksInfoFromCollection(ID3Filters) {
    var artist = ID3Filters.artist;
    var album  = ID3Filters.album;
    var genre  = ID3Filters.genre;
    var year   = ID3Filters.year;
    var where = " WHERE 1=1";

    if (artist) where += " and artist.id ="+artist;
    if (album) where += " and album.id = "+album;
    if (genre) where += " and genre.id ="+genre;
    if (year) where += " and year.id = "+year;

    var _query = "SELECT track.id,url.rpath, track.title,artist.id, artist.name, album.id, album.name, genre.name, year.name,image.path";
    _query += "    FROM tracks track JOIN albums album ON ( track.album = album.id )";
    _query += "    JOIN urls url ON (track.url = url.id)";
    _query += "    JOIN artists artist ON ( album.artist = artist.id )";
    _query += "    LEFT JOIN genres genre ON ( track.genre = genre.id )";
    _query += "    LEFT JOIN years year ON ( track.year = year.id )";
    _query += "    LEFT JOIN images image ON (album.image = image.id)";
    _query +=      where;

    var result = sql(_query);
    return result;
}

function getValuesFromTheeseTags(tagFiltersSelected,trackID) {
    var query;
    var tagIDs = new Array();
    for (i = 0; i < tagFiltersSelected.length; i++) {
        tagIDs.push(tagFiltersSelected[i].id);
    }

    if (tagFiltersSelected.length > 1) {
        query =  " select tag,value from yamo_tracktag where track =";
        query +=     " (select yatr.track from yamo_tracktag yatr";
        query +=     " where yatr.tag in ("+tagIDs.toString()+")";
        query +=     " and yatr.track = "+trackID;
        query +=     " group by yatr.track";
        query +=     " having count(*) = "+tagFiltersSelected.length+")";
        query += "     and tag in("+tagIDs.toString()+")";
    } else if (tagFiltersSelected.length == 1) {
        query =  " SELECT tag,value from yamo_tracktag where tag = "+tagIDs[0];
        query     += " AND track = "+trackID;
    }

    var result = sql(query);

    if (result.length > 0) {
        var resultArray = new Array();
        for (var i = 0; i < result.length; i++) {
            if (i % 2 == 0) {
                var resultItem = new Object();
                resultItem.name = getNameFromTagID(result[i]);
                resultItem.value = result[i+1];
                resultItem.id = result[i];
                resultArray.push(resultItem);
            }
        }
        return resultArray;
    } else {
        return null;
    }
}